CREATE DATABASE ATMSYSTEM
GO
USE ATMSYSTEM
GO

--1.Table Customer-----------------
CREATE TABLE [dbo].[Customer](
[CustID] int NOT NULL IDENTITY PRIMARY KEY([CustID]),
[Name] nvarchar(100) NOT NULL,
[Phone] varchar(50) NOT NULL,
[Email] varchar(100) NOT NULL,
[Address] nvarchar(200) NOT NULL,
)
GO
--4.Table OverDraft------
CREATE TABLE [dbo].[OverDraftLimit](
[ODID] int NOT NULL IDENTITY PRIMARY KEY([ODID]),
[Detail] varchar(30) NOT NULL,
[Value] decimal NOT NULL,
)
GO
--5.Table WithDraw Limit-------
CREATE TABLE [dbo].[WithDrawLimit](
[WDID] int NOT NULL IDENTITY PRIMARY KEY([WDID]),
[Detail] varchar(30) NULL,
[Value] decimal NOT NULL,
)
GO
--2.Table Account-----------------
CREATE TABLE [dbo].[Account](
[AccountID] int NOT NULL IDENTITY  PRIMARY KEY([AccountID]),
[CustID] int NOT NULL,
[ODID] int NOT NULL,
[WDID] int NOT NULL,
[Balance] decimal NOT NULL,
CONSTRAINT fk_Account_Cust FOREIGN KEY ([CustID])
REFERENCES [dbo].[Customer]([CustID])
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_Account_ODL FOREIGN KEY([ODID])
REFERENCES [dbo].[OverDraftLimit]([ODID])
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_Account_WDL FOREIGN KEY([WDID])
REFERENCES [dbo].[WithDrawLimit]([WDID])
ON DELETE CASCADE ON UPDATE CASCADE,
)
GO
--3.Table Card-----------------
CREATE TABLE [dbo].[Card](
[CardNo] varchar(16) NOT NULL PRIMARY KEY([CardNo]),
[Status] varchar(30) NOT NULl,
[AccountID] int NOT NULL,
[PIN] varchar(6) NOT NULL,
[StartDate] datetime NOT NULL,
[ExpiredDate] datetime NOT NULL,
[Attempt] int NOT NULL,
CONSTRAINT fk_Card_Account FOREIGN KEY([AccountID])
REFERENCES [dbo].[Account]([AccountID])
ON DELETE CASCADE ON UPDATE CASCADE,
)
GO
--6.Table ATM-------------------
CREATE TABLE [dbo].[ATM](
[ATMID] int NOT NULL IDENTITY PRIMARY KEY([ATMID]),
[Branch] nvarchar(50) NOT NULL,
[Address] nvarchar(100) NOT NULL,
)
GO
--7.Table Money-----------------
CREATE TABLE [dbo].[Money](
[MoneyID] int NOT NULL IDENTITY PRIMARY KEY([MoneyID]),
[MoneyValue] decimal NOT NULL,
)
GO
--9.Table LogType-----------------
CREATE TABLE [dbo].[LogType](
[LogTypeID] int NOT NULL IDENTITY PRIMARY KEY([LogTypeID]),
[Detail] varchar(30) NOT NULL,
[Description] nvarchar(100) NOT NULL,
)
GO
--8.Table Stock-------------------
CREATE TABLE [dbo].[Stock](
[StockID] int NOT NULL IDENTITY PRIMARY KEY([StockID]),
[MoneyID] int NOT NULL,
[ATMID] int NOT NULL,
[Quantity] int NOT NULL,
CONSTRAINT fk_Stock_Money FOREIGN KEY([MoneyID])
REFERENCES [dbo].[Money]([MoneyID])
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_Stock_ATM FOREIGN KEY([ATMID])
REFERENCES [dbo].[ATM]([ATMID])
ON DELETE CASCADE ON UPDATE CASCADE,
)
GO
--10.Table Log--------------------
CREATE TABLE [dbo].[Log](
[LogID] int NOT NULL IDENTITY PRIMARY KEY ([LogID]),
[LogTypeID] int NOT NULL,
[ATMID] int NOT NULL,
[CardNo] varchar(16) NOT NULL,
[LogDate] datetime NOT NULL,
[Amount] decimal NOT NULL,
[Details] varchar(100) NOT NULL,
CONSTRAINT fk_Log_LogType FOREIGN KEY([LogTypeID])
REFERENCES [dbo].[LogType]([LogTypeID])
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_Log_ATM FOREIGN KEY([ATMID])
REFERENCES [dbo].[ATM]([ATMID])
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_Log_Card FOREIGN KEY([CardNo])
REFERENCES [dbo].[Card]([CardNo])
ON DELETE CASCADE ON UPDATE CASCADE,
)
GO
--11.Table Config-----------------
CREATE TABLE [dbo].[Config](
[ConfigID] int NOT NULL IDENTITY PRIMARY KEY([ConfigID]),
[DateModified] datetime NOT NULL,
[MinWithDraw] decimal NOT NULL,
[MaxWithDraw] decimal NOT NULL,
)
GO

-----------------------------------

--PROC Valid Card--
CREATE PROCEDURE  sp_ValidCard 
(
	@CardNo varchar(16) OUTPUT
)
AS
	SELECT CardNo FROM dbo.[Card] WHERE CardNo = @CardNO AND Status!='Block' AND StartDate < GETDATE() AND ExpiredDate > GETDATE()
GO

--PROC Valid PIN--
CREATE PROC sp_ValidPIN
(
	@PIN varchar(6) OUTPUT,
	@CardNo varchar(16)
)
AS
	SELECT PIN FROM dbo.[Card] WHERE PIN = @PIN AND Attempt <= 3 AND CardNo=@CardNo
GO

--PROC Update attempt--
CREATE PROC sp_UpdateAttempt
(
	@CardNo varchar(16) OUTPUT
)
AS
	UPDATE dbo.[Card] SET Attempt=Attempt+1 WHERE CardNo=@CardNo
GO

--PROC Reset attempt--
CREATE PROC sp_ResetAttempt
(
	@CardNo varchar(16) OUTPUT
)
AS
	UPDATE dbo.[Card] SET Attempt=0 WHERE CardNo=@CardNo
GO

--PROC Update status--
CREATE PROC sp_UpdateStatus
  (
	@CardNo varchar(16) OUTPUT
  )
  AS
	UPDATE dbo.[Card] SET Status='Block' WHERE CardNo = @CardNo
  GO

-----update balance account transfer-------
CREATE PROCEDURE SP_UpdateAccountTransfer
@CardNo varchar(16),
@Amount decimal
AS 
	UPDATE Account 
	SET Balance= Balance-@Amount
	FROM Account 
	INNER JOIN Card 
	on Account.AccountID=Card.AccountID 
	WHERE Card.CardNo = @CardNo AND Balance>@Amount
GO

-----update balance account receive-------
CREATE PROCEDURE SP_UpdateAccountReceive
@CardNo varchar(16),
@Amount decimal
AS 
	UPDATE Account 
	SET Balance= Balance + @Amount
	FROM Account 
	INNER JOIN Card 
	on Account.AccountID=Card.AccountID 
	WHERE Card.CardNo = @CardNo 
GO

------getCustomerName----------------
CREATE PROCEDURE SP_GetCustomerName
@CardNo varchar(16)
AS
	SELECT cu.Name
	FROM Customer cu
	JOIN Account a
	ON cu.CustID = a.CustID
	JOIN Card c
	ON a.AccountID = c.AccountID
	WHERE c.CardNo = @CardNo
GO	
------getBalanceAccout----------------
CREATE PROCEDURE SP_GetBalanceAccount
@CardNo varchar(16)
AS
	SELECT a.Balance
	FROM Account a
	JOIN Card c
	ON a.AccountID = c.AccountID
	WHERE c.CardNo = @CardNo
GO
--PROC GetAccount--
CREATE PROCEDURE SP_GetAccount
@CardNo nvarchar(16)
AS
	SELECT a.AccountID, a.Balance
	FROM Account a
	JOIN Card c
	ON a.AccountID = c.AccountID
	WHERE c.CardNo = @CardNo
GO

----PROC add log---------
CREATE PROCEDURE SP_AddLog
@LogTypeID int,
@ATMID int,
@CardNo varchar(16),
@LogDate datetime,
@Amount decimal,
@Details varchar(100)
AS
	INSERT INTO [dbo].[Log] VALUES(@LogTypeID,@ATMID,@CardNo,@LogDate,@Amount,@Details)
GO

--PROC GetQuantity--
CREATE PROC [GetQuantity](@ATMID INT, @IDMoney INT)
AS
	BEGIN
		SELECT [Quantity] 
		FROM [dbo].[Stock] 
		WHERE [ATMID]=@ATMID AND [MoneyID]=@IDMoney
	END
GO

---PROC UpdateQuantity---
CREATE PROC [UpdateQuantity](@ATMID INT,@MoneyID INT,@Quantity INT)
AS
	BEGIN
		UPDATE [dbo].[Stock] 
		SET [Quantity] = [Quantity] - @Quantity 
		WHERE [ATMID] = @ATMID AND [MoneyID] = @MoneyID
	END
GO

------PROC PrintReceipt--------
CREATE PROC [PrintReceipt](@CardNo VARCHAR(16))
AS
	BEGIN
		SELECT c.CardNo,cus.Name,acc.Balance  
		FROM [dbo].[Card] c, [dbo].[Customer] cus,[dbo].[Account] acc
		WHERE c.AccountID = acc.AccountID AND acc.CustID = cus.CustID AND c.CardNo = @CardNo
	END
GO

------PROC GetMoney---------
CREATE PROC [GetMoneyInATM]
AS
	BEGIN
		SELECT SUM(s.Quantity*m.MoneyValue) AS TongTien 
		FROM [dbo].[Stock] s, [dbo].[Money] m 
		WHERE s.MoneyID = m.MoneyID
	END
GO

-------PROC WriteLog---------
CREATE PROC [WriteLog](@LogTypeID INT,@ATMID INT,@CardNo NVARCHAR(16),@LogDate DATETIME,@Amount DECIMAL(18,0),@Detail VARCHAR(100))
AS
	BEGIN
		INSERT INTO [dbo].[Log] 
		VALUES(@LogTypeID,@ATMID,@CardNo,@LogDate,@Amount,@Detail)
	END
GO

-------PROC GetAccountID---------
CREATE PROC [GetAccountID] (@CardNo NVARCHAR(16))
AS
	BEGIN
		SELECT [AccountID] 
		FROM [dbo].[Card] 
		WHERE [CardNo] = @CardNo
	END
GO

-------PROC GetBalance----------
CREATE PROC [GetBalance](@AccountID INT)
AS
	BEGIN
		SELECT Balance FROM [dbo].[Account] WHERE AccountID = @AccountID
	END
GO

-------PROC UpdateBalance---------
CREATE PROC [UpdateBalance](@AccountID INT, @Amount DECIMAL(18,0))
AS
	BEGIN
		UPDATE [dbo].[Account] 
		SET [Balance] = [Balance] - @Amount
		WHERE [AccountID] =@AccountID
	END
GO

----PROC Viewhistory----
CREATE PROCEDURE dbo.SP_gridview_paging_UCViewHitory
	(
		@CardNo	varchar(16),
		@Ngay int
	)
	AS

		IF(@Ngay = 7)
		(
			SELECT  l.ATMID, l.LogDate, l.Amount, l.Details, lt.Description
			FROM dbo.Card c
			INNER JOIN dbo.Log l
			ON c.CardNo = l.CardNo
			JOIN dbo.LogType lt
			ON lt.LogTypeID = l.LogTypeID
			 WHERE c.CardNo = @CardNo AND DATEDIFF(DAY, l.LogDate, GETDATE()) <=7
		 )
		 IF(@Ngay = 30)
		(
			SELECT  l.ATMID, l.LogDate, l.Amount, l.Details, lt.Description
			FROM dbo.Card c
			INNER JOIN dbo.Log l
			ON c.CardNo = l.CardNo
			JOIN dbo.LogType lt
			ON lt.LogTypeID = l.LogTypeID
			 WHERE c.CardNo = @CardNo AND DATEDIFF(DAY, l.LogDate, GETDATE()) <=30
		 )
		 IF(@Ngay = 120)
		(
			SELECT   l.ATMID, l.LogDate, l.Amount, l.Details, lt.Description
			FROM dbo.Card c
			INNER JOIN dbo.Log l
			ON c.CardNo = l.CardNo
			JOIN dbo.LogType lt
			ON lt.LogTypeID = l.LogTypeID
			 WHERE c.CardNo = @CardNo AND DATEDIFF(DAY, l.LogDate, GETDATE()) <=120
		 )
		 IF(@Ngay = 180)
		(
			SELECT  l.ATMID, l.LogDate, l.Amount, l.Details, lt.Description
			FROM dbo.Card c
			INNER JOIN dbo.Log l
			ON c.CardNo = l.CardNo
			JOIN dbo.LogType lt
			ON lt.LogTypeID = l.LogTypeID
			 WHERE c.CardNo = @CardNo AND DATEDIFF(DAY, l.LogDate, GETDATE()) <=180
		 )
		 IF(@Ngay = 360)
		(
			SELECT  l.ATMID, l.LogDate, l.Amount, l.Details, lt.Description
			FROM dbo.Card c
			INNER JOIN dbo.Log l
			ON c.CardNo = l.CardNo
			JOIN dbo.LogType lt
			ON lt.LogTypeID = l.LogTypeID
			 WHERE c.CardNo = @CardNo AND DATEDIFF(DAY, l.LogDate, GETDATE()) <=360
		 )
		 IF(@Ngay = 720)
		(
			SELECT  l.ATMID, l.LogDate, l.Amount, l.Details, lt.Description
			FROM dbo.Card c
			INNER JOIN dbo.Log l
			ON c.CardNo = l.CardNo
			JOIN dbo.LogType lt
			ON lt.LogTypeID = l.LogTypeID
			 WHERE c.CardNo = @CardNo AND DATEDIFF(DAY, l.LogDate, GETDATE()) <=720
		 )
GO